package com.csi.sai.bl.demand;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.sql.DataSource;

import com.csi.sai.data.entities.Cliente;
import com.csi.sai.data.entities.Contacto;
import com.csi.sai.data.entities.Demand;
import com.csi.sai.data.entities.Types;
import com.csi.sai.utilities.Constants;
import com.csi.sai.utilities.SystemMessages;
import com.csi.sai.utilities.Utilities;
import com.csi.sai.utilities.properties.ResourceBundleManager;
import com.csi.sai.utilities.templates.SelectTemplate;
import com.csi.sai.utilities.templates.components.DivComponents;
import com.csi.sai.utilities.templates.reports.DemandTemplate;
import com.csi.sai.utilities.templates.selects.ComboClient;
import com.csi.sai.utilities.templates.selects.ComboCorreo;
import com.csi.sai.utilities.templates.selects.ComboTecnologia;
import com.csi.sai.utilities.templates.selects.ComboTelefono;
import com.csi.sai.utilities.templates.selects.ComponentTemplate;

public class DemandProcess {

	protected DataSource ds;
	private String strClientSelect;
	private String HTMLCode;
	private int idGenerated;
	
	public String getHTMLCode() { return this.HTMLCode; }
	public int getIdGenerated() { return this.idGenerated; }
	
	public DemandProcess(){}
	
	public DemandProcess(DataSource ds) {
		this.ds = ds;
		this.HTMLCode = "";
	}

	public boolean catalogDemand(Demand demand, int type) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_DEMAND);
			ps.setInt(1, type);
			ps.setInt(2, demand.getIdSalesperson());
			ps.setInt(3, demand.getClient());
			ps.setString(4, demand.getContact());
			ps.setString(5, demand.getVacancy());
			ps.setString(6, demand.getProfile());
			ps.setString(7, demand.getCreation());
			ps.setString(8, demand.getPriorityDate());
			ps.setInt(9, demand.getDuration());
			ps.setInt(10, demand.getMensualRate());
			ps.setInt(11, demand.getPipeLine());
			ps.setString(12, demand.getFinshDate());
			ps.setInt(13, demand.getNumVacancy());
			ps.setInt(14, demand.getIdDemand());
			ps.setString(15, getStatusBy(type));
			
			ps.setString(16, demand.getExperience());
			ps.setString(17, demand.getRequest());
			ps.setString(18, demand.getResidence());
			ps.setString(19, demand.getLenguaje());
			ps.setInt(20, demand.isAvToTravel());

			if(type==1) {
				rs = ps.executeQuery();
				rs.next();
				this.idGenerated = rs.getInt(1); 
			} else {
				ps.execute();
			}
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
			SystemMessages.setErrorMessage(ResourceBundleManager.getMessageFromResourceBundle("msg004")+". "+e.getMessage());
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	private String getStatusBy(int type) {
		switch (type) {
		case 1: return "En Espera";		 
		case 2: return "En Proceso";	
		default: return "Inactiva";
		}
	}
	
	public boolean loadSelects(int type, int idUser, int idCliente, boolean editMode, Demand d) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int idSelected = 0;
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_COMBOS);
			switch (type) {
			case 1:
				ps.setInt(1, type);
				ps.setInt(2, 2);
				idSelected = d.getIdSalesperson();
				break;
			case 2:
				ps.setInt(1, type);
				ps.setInt(2, idUser);
				idSelected = d.getClient();
				break;
			case 3:
				ps.setInt(1, type);
				ps.setInt(2, idCliente);
				idSelected = d.getIdContact();
				break;
			default:
				break;
			}
			rs = ps.executeQuery();
			
			ComponentTemplate combo = Utilities.getTemplateBy(type);
			if(editMode){
				combo.setRS(rs, idSelected);
			} else {
				combo.setRS(rs);
			}
			this.HTMLCode = combo.getComponentGeneric();
			
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			SystemMessages.setErrorMessage(ResourceBundleManager.getMessageFromResourceBundle("msg001"));
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	public boolean getCombosClient() {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		
		try {
			conn = ds.getConnection();
			
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
			generateDinamicCombos(rs, ps);
			HTMLCode.replace("{selected}", "");
			
			loadClientSelect(conn);
			HTMLCode += "~";
			HTMLCode += strClientSelect;
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		
		return false;
	}
	
	private void generateDinamicCombos(ResultSet rs, PreparedStatement ps) throws SQLException {
		
		Utilities.generatePreparedStatement(ps, 1, new Types());
		
		rs = ps.executeQuery();
		ComboTelefono cPhone = new ComboTelefono();
		cPhone.setRS(rs);
		rs.close();
		HTMLCode += cPhone.getComponentGeneric();
		HTMLCode += "~";
		
		ps.getMoreResults();
		rs = ps.getResultSet();
		ComboCorreo cMail = new ComboCorreo();
		cMail.setRS(rs);
		rs.close();
		HTMLCode += cMail.getComponentGeneric();
		HTMLCode += "~";
		
		ps.getMoreResults();
		rs = ps.getResultSet();
		ComboTecnologia cTech = new ComboTecnologia();
		cTech.setRS(rs);
		rs.close();
		HTMLCode += cTech.getComponentGeneric();
		
	}
	/*
	private boolean loadSalespersonSelect(Connection conn) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_UTILITIES);
			ps.setInt(1, Constants.TRAN_LOADSALESPERSON_SELECT);
			rs = ps.executeQuery();
			
			StringBuffer sb = new StringBuffer();
			sb.append(SelectTemplate.salesOptionTemplate.
						replace("{id}", "0").
						replace("{option}", "Selecciona"));
			while(rs.next()) {
				sb.append(SelectTemplate.salesOptionTemplate.
						replace("{id}", rs.getString(1)).
						replace("{option}", rs.getString(2) +" "+rs.getString(3)));
			}
			strSalesPersonSelect = SelectTemplate.salesSelectSalespersonTemplate.replace("{options}", sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
			SystemMessages.setErrorMessage(ResourceBundleManager.getMessageFromResourceBundle("msg003"));
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
		}
		return true;
	}
	*/
	private boolean loadClientSelect(Connection conn) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_UTILITIES);
			ps.setInt(1, Constants.TRAN_LOADCLIENT_SELECT);
			rs = ps.executeQuery();
			
			StringBuffer sb = new StringBuffer();
			sb.append(SelectTemplate.salesOptionTemplate.
					replace("{id}", "0").
					replace("{option}", "Selecciona"));
			while(rs.next()) {
				sb.append(SelectTemplate.salesOptionTemplate.
						replace("{id}", rs.getString(1)).
						replace("{option}", rs.getString(2)));
			}
			strClientSelect = SelectTemplate.salesSelectClientTemplate.replace("{options}", sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
			SystemMessages.setErrorMessage(ResourceBundleManager.getMessageFromResourceBundle("msg002"));
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
		}
		return true;
	}
	
	public boolean catalogClient(int type, int stype, Cliente client) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_CLIENT);
			client.setFechaAlta(Utilities.getTodayByFormat(Utilities.formatDB));
			Utilities.generatePreparedStatement(ps, type, client);
			
			
			if(type != 4) { /** Guarda o actualiza **/
				rs = ps.executeQuery();
				
				/** Se obtiene el id Generado del cliente creado **/
				rs.next();
				this.idGenerated = rs.getInt(1);
				
				rs.close();
				ps.close();
				
				/** Se crean mails relacionados al Cliente **/
				for(String mail : client.getCorreo()) {
					stype = mail.split("_")[2].equals("0") ? Constants.TRAN_TYP_CREATEDATA : Constants.TRAN_TYP_UPDATEDATA;
					
					Types t = new Types();
					ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
					
					t.setEntity("correo");
					t.setIdType(Integer.parseInt(mail.split("_")[0]));
					t.setData(mail.split("_")[1]);
					t.setIdClient(idGenerated);
					if(stype==5) {
						t.setIdData(Integer.parseInt(mail.split("_")[2]));
					}
					Utilities.generatePreparedStatement(ps, stype, t);
					
					ps.execute();
					ps.close();
				}
				
				/** Se crean telefonos relacionados al Cliente **/
				for(String phone : client.getTelefono()) {
					stype = phone.split("_")[2].equals("0") ? Constants.TRAN_TYP_CREATEDATA : Constants.TRAN_TYP_UPDATEDATA;
					
					Types t = new Types();
					ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
					
					t.setEntity("telefono");
					t.setIdType(Integer.parseInt(phone.split("_")[0]));
					t.setData(phone.split("_")[1]);
					t.setIdClient(idGenerated);
					if(stype==5) {
						t.setIdData(Integer.parseInt(phone.split("_")[2]));
					}
					Utilities.generatePreparedStatement(ps, stype, t);
					
					ps.execute();
					ps.close();
				}
				
				/** Se crean las tecnologias relacionadas al Cliente **/
				for(String tech : client.getTecnologia()) {
					stype = tech.split("_")[2].equals("0") ? Constants.TRAN_TYP_CREATEDATA : Constants.TRAN_TYP_UPDATEDATA;
					
					Types t = new Types();
					ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
					
					t.setEntity("tecnologia");
					t.setIdType(Integer.parseInt(tech.split("_")[0]));
					t.setData(tech.split("_")[1]);
					t.setIdClient(idGenerated);
					t.setDate(Utilities.getTodayByFormat(Utilities.formatDB));
					if(stype==5) {
						t.setIdData(Integer.parseInt(tech.split("_")[2]));
					}
					Utilities.generatePreparedStatement(ps, stype, t);
					
					ps.execute();
					ps.close();
				}
				
				/** Se obtienen los registos recien insertados **/
				Types t = new Types();
				ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
				t.setIdClient(idGenerated);
				t.setEntity(Constants.TRAN_TYP_ENTCLIENT);
				Utilities.generatePreparedStatement(ps, 6, t);
				
				rs = ps.executeQuery();
				
				/** El parametro 1 indica que es cliente **/
				HTMLCode = Utilities.getEntitiesEditMode(rs, ps, conn, 1);
				//System.out.println(HTMLCode);
				
			} else { /** Obtiene selects **/
				rs = ps.executeQuery();
				generateHTMLCode(rs);
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}

	public boolean catalogContact(int type, int stype, Contacto contacto) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_CONTACT);
			contacto.setFechaCreacion(Utilities.getTodayByFormat(Utilities.formatDB));
			Utilities.generatePreparedStatement(ps, type, contacto);
			
			rs = ps.executeQuery();
			
			rs.next();
			int idGenerated = rs.getInt(1);
			
			rs.close();
			ps.close();
			
			Types t1 = new Types();
			t1.setIdContact(idGenerated);
			Utilities.createEntitie(Constants.ENT_TYP_PHONE, stype, t1, contacto.getTelefono(), ps, conn);
			Utilities.createEntitie(Constants.ENT_TYP_MAIL, stype, t1, contacto.getCorreo(), ps, conn);
			
			/** Se obtienen los registos recien insertados **/
			Types t = new Types();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
			t.setEntity(Constants.TRAN_TYP_ENTCONTACT);
			t.setIdContact(idGenerated);
			Utilities.generatePreparedStatement(ps, 6, t);
			
			rs = ps.executeQuery();
			
			/** El parametro 2 indica que es contacto **/
			HTMLCode = Utilities.getEntitiesEditMode(rs, ps, conn, 2);
			
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}

	private void generateHTMLCode(ResultSet rs) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("<select id=\"{selectID}\" style=\"width: 150px;\">");
		sb.append("<option id=\"0\">Selecciona</option>");
		while(rs.next()) {
			sb.append("<option id=\""+rs.getInt(1)+"\">"+rs.getString(2)+"</option>");
		}
		sb.append("</select>");
		
		this.HTMLCode = sb.toString();
	}
	
	
	/**
	 * @param type
	 * @param demandToReassign
	 * @param comment
	 * @param idEmployee 
	 * @return
	 * Campo de duracion es autorizada/no autorizada 1/0
	 */
	public boolean reassignDemand(int type, int demandToReassign, String comment, int idEmployee) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_DEMAND);
			Demand demand = new Demand();
			demand.setIdDemand(demandToReassign);
			demand.setDuration(0);
			demand.setProfile(comment);
			demand.setFinshDate(Utilities.getTodayByFormat(Utilities.formatDB));
			demand.setStatus(Constants.STS_DEMAND_REASSIGN);
			demand.setIdSalesperson(idEmployee);
			Utilities.generatePreparedStatement(ps, type, demand);
			ps.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	public boolean getComponent(String parentID) {
		String sqlQuery = "select * from ";
		String template = null;
		/**************************************************** CLIENTE ***/
		if(parentID.equals("cdPhone")) {
			template = DivComponents.phoneDivClient;
			sqlQuery += "TipoTelefono";
		} else if(parentID.equals("cdMail")) {
			template = DivComponents.mailDivClient;
			sqlQuery += "TipoCorreo";
		} else if(parentID.equals("cdTech")) {
			template = DivComponents.techDivClient;
			sqlQuery += "TipoTecnologia";
		/**************************************************** CONTACTO ***/
		} else if(parentID.equals("ctdPhone")) {
			template = DivComponents.phoneDivContact;
			sqlQuery += "TipoTelefono";
		} else if(parentID.equals("ctdMail")) {
			template = DivComponents.mailDivContact;
			sqlQuery += "TipoCorreo";
		/**************************************************** CANDIDATO ***/	
		} else if(parentID.equals("cadPhone")) {
			template = DivComponents.phoneDivCandidate;
			sqlQuery += "TipoTelefono";
		} else if(parentID.equals("cadMail")) {
			template = DivComponents.mailDivCandidate;
			sqlQuery += "TipoCorreo";
		} else if(parentID.equals("cadTech")) {
			template = DivComponents.techDivCandidate;
			sqlQuery += "TipoTecnologia";
		}
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(sqlQuery);
			rs = ps.executeQuery();
			
			StringBuffer sb = new StringBuffer();
			while(rs.next()) {
				sb.append("<option id=\""+rs.getInt(1)+"\">"+rs.getString(2)+"</option>");
			}
			
			HTMLCode = template
					.replace("{options}", sb.toString())
					.replace("{value}", "")
					.replace("{id}", "0");
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	public boolean deleteEntitie(String dbId, String entitie) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String tbl = null;
		String id = null;
		
		if(entitie.toLowerCase().contains("phone")) {
			tbl = "Telefono";
			id = "idTelefono";
		} else if(entitie.toLowerCase().contains("mail")) {
			tbl = "Correo";
			id = "idCorreo";
		} else if (entitie.toLowerCase().contains("tech")) {
			tbl = "Tecnologia";
			id = "idTecnologia";
		}
		String queryDB = "delete "+tbl+" where "+id+"="+dbId;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(queryDB);
			
			ps.execute();
			return true; 
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
}
